#!/use/bin/env python
# encoding:utf-8

'''删除数据库不需要的厂商和MEID数据,将数据库按照厂商排序后导出为EXCLE'''

import pymysql
import xlwt
import os
import time

#处理数据
cs = ["NCMC","RIM",'SHARP公司',"中卫莱康","中辰","中锘基雅","丰尚","众鑫百合","优易","优派环宇","佰利","佳斯特","信云","元一时代","全盈通讯","凤腾伟业","创维","华唐","华录","华硕","华立","华立时代","华翼兴达","南极星","博瑞","卡尔电气","卡美欧","吉邦","启东优思","和信","唐为","垦鑫达","基思瑞","夏新","夏朗","大唐","大成","大显","大显泛泰","大诚联合","天津通广","好利通","威盛","威铂通讯","展翼","库麦","康佳","彤霖","微网信通","惠州德赛电子","扬州万事通","振华","振华欧比","掌中宝","斐讯通信","易丰展业","暂无","未知厂商","杭州东信百丰","桑菲","比酷","洪洋伟业","绵阳国虹","美翼景业","美菱","翼鸣时代","芯洲源","英华达","语信时代","赛鸿通讯","锐族","青橙","首信","高通无线","鹏智","齐乐","中维恒泰","丰尚","乐游互联","京瓷","京瓷振华","深圳峰达通"," ","null"]
meid = ['00000000000000','0','null']


try:
    conn = pymysql.connect(host="127.0.0.1", user="root", passwd="123456", db="test", port=3306, charset="utf8")
    cur = conn.cursor()
    for line in cs:
        #cur.execute('DELETE FROM sheet1 WHERE changshang = %s'%line )
        sql = 'DELETE FROM sheet1 WHERE changshang = \'%s\''%line
        cur.execute(sql)
    for a in meid:
        sql2 = 'DELETE FROM sheet1 WHERE MEID = \'%s\''%a
        cur.execute(sql2)
    cur.execute('SELECT * FROM Sheet1 ORDER BY changshang DESC;')
    '''将数据库内容读取到表格    '''
    data = cur.fetchall()   #获取所有处理后的数据
    conn.commit()     #提交上面的数据操作
    print('*******数据库命令提交成功*******data.py***')
except:
    conn.rollback()  #出错之后数据回滚
finally:
    cur.close()
    conn.close()

# print data[0][0] ,len(data), len(data[0])    #显示数据信息
print( '行数为：' ,len(data),'列数为：', len(data[0]))    #显示数据信息
i = 0
y = 0
workbook = xlwt.Workbook(encoding='ascii')   #设置表格字符集
worksheet = workbook.add_sheet('data')  #设置工作簿名称

# output.write(0,1,'aaa')
# 利用双重for循环将获取到字符集内容写入到工作簿
for sj in data:
    for a in data[0]:
        # print i, y
        worksheet.write(i,y,data[i][y])   #将相应内容写入到工作薄
        y = y+1

    i = i + 1
    y = 0   #每次让Y的值为0   即可实现y不断的从0到3

local_time = time.strftime('%Y%m%d',time.localtime())  #设置时间格式
workbook.save('d:/手机延保%s数据.xls'%local_time)   #将表格内容保存在本地
print ('******表格写入成功*******在D盘')


